﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Transactions;

namespace CommonDevelop
{
    /// <summary>
    /// 转换扩展类
    /// </summary>
    public static class SqlHelper
    {
        /// <summary>
        /// 组装存储过程的执行语句
        /// </summary>        
        public static List<SqlParameter> GetSQLParametersOut64(string SP_Name, Dictionary<string, object> Parameters, List<string> OutputParameters, out string exec)
        {
            exec = string.Empty;

            List<SqlParameter> IntoParameter = SqlHelper.DicToParameters(Parameters);
            if (OutputParameters == null || OutputParameters.Count < 1)
            {
                OutputParameters = new List<string>();
            }
            else
            {
                foreach (SqlParameter v in IntoParameter)
                {
                    foreach (var kv in OutputParameters)
                    {
                        string PName = null;
                        if (kv.Contains("@"))
                            PName = kv;
                        else
                            PName = "@" + kv;

                        if (v.ParameterName.Equals(PName))
                        {
                            v.Direction = ParameterDirection.Output;
                            v.Size = 64;
                        }
                    }
                }
            }

            if (IntoParameter.Count > 0)
            {
                if (IntoParameter.FirstOrDefault(a => a.Direction == ParameterDirection.ReturnValue) == null)
                {
                    var p = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
                    p.Direction = ParameterDirection.ReturnValue;
                    IntoParameter.Add(p);
                }

                StringBuilder txt = new StringBuilder();
                foreach (var paremeter in IntoParameter)
                    if (paremeter.Direction == ParameterDirection.Input)
                        txt.Append(paremeter.ParameterName + " ,");

                foreach (var paremeter in IntoParameter)
                    if (paremeter.Direction == ParameterDirection.Output)
                        txt.Append(paremeter.ParameterName + " out,");

                txt = txt.Remove(txt.Length - 1, 1);
                exec = string.Format("exec [{0}] {1}", SP_Name, txt.ToString());
            }

            return IntoParameter;
        }
        private static List<SqlParameter> DicToParameters(Dictionary<string, object> Parameters)
        {
            if (Parameters == null)
                return new List<SqlParameter>();

            List<SqlParameter> parLs = new List<SqlParameter>();
            foreach (var v in Parameters)
            {
                string PName = null;
                if (v.Key.Contains("@"))
                    PName = v.Key;
                else
                    PName = "@" + v.Key;

                parLs.Add(new SqlParameter(PName, v.Value));
            }

            return parLs;
        }

        /// <summary>
        /// 将DataTable数据源转换成实体类 DataTable-->List
        /// </summary>
        public static List<T> DataTableToModels<T>(DataTable dt) where T : class, new()
        {
            List<T> ts = new List<T>();// 定义集合
            foreach (DataRow dr in dt.Rows)
            {
                T t = new T();
                PropertyInfo[] propertys = t.GetType().GetProperties(); // 获得此模型的公共属性
                foreach (PropertyInfo pi in propertys)
                {
                    if (dt.Columns.Contains(pi.Name))
                    {
                        if (!pi.CanWrite)
                            continue;

                        var value = dr[pi.Name];
                        if (value != DBNull.Value)
                        {
                            Type pt = pi.PropertyType;
                            if (pt.IsGenericType && pt.GetGenericTypeDefinition() == typeof(Nullable<>))
                                pt = pt.GetGenericArguments()[0];

                            if (pt.IsEnum)
                            {
                                pi.SetValue(t, Enum.Parse(pt, value.ToString()), null);
                            }
                            else if (pt == typeof(DateTime))
                            {
                                pi.SetValue(t, DateTime.Parse(value.ToString()), null);
                            }
                            else if (pt == typeof(Guid))
                            {
                                pi.SetValue(t, Guid.Parse(value.ToString()), null);
                            }
                            else if (pt == typeof(decimal))
                            {
                                pi.SetValue(t, decimal.Parse(value.ToString()), null);
                            }
                            else if (pt == typeof(int))
                            {
                                pi.SetValue(t, int.Parse(value.ToString()), null);
                            }
                            else if (pt == typeof(float))
                            {
                                pi.SetValue(t, float.Parse(value.ToString()), null);
                            }
                            else if (pt == typeof(long))
                            {
                                pi.SetValue(t, long.Parse(value.ToString()), null);
                            }
                            else if (pt == typeof(bool))
                            {
                                pi.SetValue(t, bool.Parse(value.ToString()), null);
                            }
                            else if (pt == typeof(string))
                            {
                                pi.SetValue(t, value.ToString(), null);
                            }
                            else
                            {
                                pi.SetValue(t, value, null);
                            }
                        }
                    }
                }
                ts.Add(t);
            }
            return ts;
        }
        /// <summary>
        ///  将IDataReader转换为DataTable
        /// </summary>
        public static DataTable IDataReaderToDataTable(IDataReader reader)
        {
            using (reader)
            {
                DataTable objDataTable = new DataTable("Table");
                int intFieldCount = reader.FieldCount;
                for (int intCounter = 0; intCounter < intFieldCount; ++intCounter)
                {
                    objDataTable.Columns.Add(reader.GetName(intCounter).ToLower(), reader.GetFieldType(intCounter));
                }
                objDataTable.BeginLoadData();
                object[] objValues = new object[intFieldCount];
                while (reader.Read())
                {
                    reader.GetValues(objValues);
                    objDataTable.LoadDataRow(objValues, true);
                }
                reader.Close();
                reader.Dispose();
                objDataTable.EndLoadData();
                return objDataTable;
            }
        }

        /// <summary>
        /// 获取实体类键值（没有缓存）
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="entity">实体对象</param>
        /// <returns></returns>
        public static Hashtable GetPropertyInfo<T>(T entity)
        {
            Type type = entity.GetType();
            object CacheEntity = null;
            if (CacheEntity == null)
            {
                Hashtable ht = new Hashtable();
                PropertyInfo[] props = type.GetProperties();
                foreach (PropertyInfo prop in props)
                {
                    if (prop.PropertyType.IsClass && !prop.PropertyType.Equals(typeof(string)))
                        continue;

                    if (prop.PropertyType.IsInterface)
                        continue;

                    bool flag = true;
                    foreach (Attribute attr in prop.GetCustomAttributes(true))
                    {
                        NotMappedAttribute notMapped = attr as NotMappedAttribute;
                        if (notMapped != null)
                        {
                            flag = false;
                            break;
                        }
                        KeyAttribute keyattribute = attr as KeyAttribute;
                        if (keyattribute != null)
                        {
                            flag = false;
                            break;
                        }
                    }

                    if (flag)
                    {
                        string name = prop.Name;
                        object value = prop.GetValue(entity, null);
                        ht[name] = value;
                    }
                }
                return ht;
            }
            else
            {
                return (Hashtable)CacheEntity;
            }
        }

        /// <summary>
        /// 获取实体对象Key
        /// </summary>
        public static string GetPrimaryKey<T>() where T : class
        {
            return GetPrimaryKey(typeof(T));
        }
        public static string GetPrimaryKey(Type type)
        {
            PropertyInfo[] props = type.GetProperties();
            foreach (PropertyInfo prop in props)
            {
                foreach (Attribute attr in prop.GetCustomAttributes(true))
                {
                    KeyAttribute keyattribute = attr as KeyAttribute;
                    if (keyattribute != null)
                    {
                        return prop.Name;
                    }
                }
            }
            return string.Empty;
        }
        /// <summary>
        /// 获取实体主键的值
        /// </summary>
        public static string GetPrimaryKeyValue<T>(T enValue) where T : class
        {
            if (enValue == null)
                return string.Empty;

            var propertyName = GetPrimaryKey<T>();
            object Val = null;
            PropertyInfo[] ps = enValue.GetType().GetProperties();
            foreach (PropertyInfo p in ps)
            {
                if (p.Name == propertyName)
                {
                    Val = p.GetValue(enValue, null);
                    break;
                }
            }

            if (Val == null)
                return string.Empty;
            else
                return Val.ToString();
        }

        /// <summary>
        /// 获取实体代表的表名
        /// </summary>
        public static string GetTableName(Type entityType)
        {
            var tablename = entityType.GetCustomAttributes(typeof(TableAttribute), true);
            if (tablename != null && tablename.Length > 0)
                return ((TableAttribute)tablename[0]).Name;
            else
                return entityType.Name;
        }
        public static string GetTableName<T>() where T : class
        {
            return GetTableName(typeof(T));
        }

        #region---ex操作---

        public static List<string> GetFields<T>(bool isFullName) where T : class
        {
            var fields = typeof(T).GetProperties();
            List<PropertyInfo> InfoLS = new List<PropertyInfo>();
            foreach (var i in fields)
            {
                if (i.GetCustomAttributes(true).OfType<NotMappedAttribute>().Any())
                    continue;

                InfoLS.Add(i);
            }

            var result = new List<string>();
            if (isFullName)
            {
                var tablename = GetTableName<T>();
                result.AddRange(InfoLS.Select(i => tablename + "." + i.Name));
                return result;
            }

            result.AddRange(InfoLS.Select(i => i.Name));
            return result;
        }
        public static string GetFiledString<T>(bool isFullName = true) where T : class
        {
            var list = GetFields<T>(isFullName).ToArray();
            var result = string.Join(",", list);
            return result;
        }

        /// <summary>
        /// 获取Entity实例的字段名和值（用于更新和插入数据）
        /// </summary>
        public static Dictionary<string, object> GetKeyValue(object obj)
        {
            var data = new Dictionary<string, object>();
            foreach (var i in obj.GetType().GetProperties())
            {
                if (IsContainsKeyAttribute(i.GetCustomAttributes(true)))
                    continue;

                if (IsContainsNotMappedAttribute(i.GetCustomAttributes(true)))
                    continue;

                bool IsCanNull = false;
                var pi = obj.GetType().GetProperty(i.Name);
                Type pt = pi.PropertyType;
                if (pt.IsGenericType && pt.GetGenericTypeDefinition() == typeof(Nullable<>))
                {
                    pt = pt.GetGenericArguments()[0];
                    IsCanNull = true;
                }

                var value = pi.GetValue(obj, null);
                if (value == null && !IsCanNull)
                    if (pt == typeof(DateTime))
                        value = DateTime.Today;

                data.Add("[" + i.Name + "]", value);
            }
            return data;
        }
        /// <summary>
        /// 是否包含自增键，在插入表时可以跳过自增键的设置
        /// </summary>
        private static bool IsContainsKeyAttribute(IEnumerable<object> attrs)
        {
            return attrs.OfType<KeyAttribute>().Any();
        }
        /// <summary>
        /// 是否视图
        /// </summary>
        private static bool IsContainsNotMappedAttribute(IEnumerable<object> attrs)
        {
            return attrs.OfType<NotMappedAttribute>().Any();
        }

        #endregion

        /// <summary>
        /// 拼装分页sql
        /// </summary>
        public static string GetMappingStrategyPage(Type t, string WherePart, string SortField, bool IsAsc, int pageIndex, int pageSize, out string totalRecordSQL)
        {
            totalRecordSQL = null;
            if (t == null)
                return null;

            if (string.IsNullOrEmpty(WherePart))
            {
                WherePart = "1=1";
            }

            string sqlCount = "select COUNT(0) AS n from {0}(NOLOCK) AS m WHERE 1=1 AND {1} {2}";
            string sql = "WITH temp AS (select *,row_number() over(order by {1}) as exz from {0}(NOLOCK) AS m WHERE 1=1 AND {2} {3})select * from temp where exz between ({4}-1)*{5}+1 and {4}*{5} ORDER BY {1} ";

            if (string.IsNullOrEmpty(SortField))
            {
                SortField = GetPrimaryKey(t);
                IsAsc = false;
            }

            if (string.IsNullOrEmpty(SortField))
                return null;

            if (IsAsc)
                SortField = SortField + " asc";
            else
                SortField = SortField + " desc";

            string sqlEx = string.Empty;
            sqlCount = string.Format(sqlCount, t.Name, WherePart, sqlEx);
            sql = string.Format(sql, t.Name, SortField, WherePart, sqlEx, pageIndex.ToString(), pageSize.ToString());

            totalRecordSQL = sqlCount;
            return sql;
        }

        public static string SqlPageSql(Type t, string SelectSql, string SortField, bool IsAsc, int pageIndex, int pageSize, out string totalRecordSQL)
        {
            totalRecordSQL = null;
            if (string.IsNullOrEmpty(SelectSql))
            {
                SelectSql = "SELECT * FROM " + t.Name + "(NOLOCK) WHERE 1<>1";
                SortField = string.Empty;
            }

            string sqlCount = "select COUNT(0) AS n from ({0}) AS m";
            string sql = "WITH temp AS (select *,row_number() over(order by {1}) as exz from ({0}) AS m)select * from temp where exz between ({2}-1)*{3}+1 and {2}*{3} ORDER BY {1} ";

            if (string.IsNullOrEmpty(SortField))
            {
                SortField = GetPrimaryKey(t);
                IsAsc = false;
            }
            else
            {
                string tableName = t.Name;
                SortField = SortField.Replace(tableName + ".", "");
            }

            if (string.IsNullOrEmpty(SortField))
                return null;

            if (IsAsc)
                SortField = SortField + " asc";
            else
                SortField = SortField + " desc";

            string sqlEx = string.Empty;
            sqlCount = string.Format(sqlCount, SelectSql);
            sql = string.Format(sql, SelectSql, SortField, pageIndex.ToString(), pageSize.ToString());

            totalRecordSQL = sqlCount;
            return sql;
        }
        public static string SqlPageSql(string SelectSql, string SortField, bool IsAsc, int pageIndex, int pageSize, out string totalRecordSQL)
        {
            totalRecordSQL = null;
            if (string.IsNullOrEmpty(SelectSql))
            {
                SelectSql = string.Empty;
                SortField = string.Empty;
            }

            if (string.IsNullOrEmpty(SortField))
                return null;

            string sqlCount = "select COUNT(0) AS n from ({0}) AS m";
            string sql = "WITH temp AS (select *,row_number() over(order by {1}) as exz from ({0}) AS m)select * from temp where exz between ({2}-1)*{3}+1 and {2}*{3} ORDER BY {1} ";

            if (IsAsc)
                SortField = SortField + " asc";
            else
                SortField = SortField + " desc";

            string sqlEx = string.Empty;
            sqlCount = string.Format(sqlCount, SelectSql);
            sql = string.Format(sql, SelectSql, SortField, pageIndex.ToString(), pageSize.ToString());

            totalRecordSQL = sqlCount;
            return sql;
        }

        public static string MySqlPageSql(Type t, string SelectSql, string SortField, bool IsAsc, int pageIndex, int pageSize, out string totalRecordSQL)
        {
            totalRecordSQL = null;
            if (string.IsNullOrEmpty(SelectSql))
            {
                SelectSql = "SELECT * FROM " + t.Name + " WHERE 1<>1";
                SortField = string.Empty;
            }

            string sqlCount = "select COUNT(1) AS n from ({0}) AS m";
            string sql = "SELECT * FROM ({0}) AS A {1} LIMIT {2},{3}";

            if (string.IsNullOrEmpty(SortField))
            {
                SortField = GetPrimaryKey(t);
                IsAsc = false;
            }
            else
            {
                string tableName = t.Name;
                SortField = SortField.Replace(tableName + ".", "");
            }

            if (string.IsNullOrEmpty(SortField))
                return null;

            if (IsAsc)
                SortField = "ORDER BY " + SortField + " ASC";
            else
                SortField = "ORDER BY " + SortField + " DESC";

            string sqlEx = string.Empty;
            sqlCount = string.Format(sqlCount, SelectSql);
            sql = string.Format(sql, SelectSql, SortField, pageIndex.ToString(), pageSize.ToString());

            totalRecordSQL = sqlCount;
            return sql;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        public static DataSet RunP(string connectionString, string SP_Name, Dictionary<string, object> Parameters, List<string> OutputParameters, out Tuple<int, Dictionary<string, object>> ReturnVal)
        {
            ReturnVal = null;
            List<SqlParameter> IntoParameter = DicToParameters(Parameters);
            if (OutputParameters == null || OutputParameters.Count < 1)
            {
                OutputParameters = new List<string>();
            }
            else
            {
                foreach (SqlParameter v in IntoParameter)
                {
                    foreach (var kv in OutputParameters)
                    {
                        string PName = null;
                        if (kv.Contains("@"))
                            PName = kv;
                        else
                            PName = "@" + kv;

                        if (v.ParameterName.Equals(PName))
                        {
                            v.Direction = ParameterDirection.Output;
                            v.Size = 64;
                        }
                    }
                }
            }

            if (IntoParameter.FirstOrDefault(a => a.Direction == ParameterDirection.ReturnValue) == null)
            {
                var p = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
                p.Direction = ParameterDirection.ReturnValue;
                IntoParameter.Add(p);
            }

            DataSet ds = null;
            Dictionary<string, object> OutputParameter = null;

            try
            {
                using (var scope = new TransactionScope())
                {
                    ds = RunProcedure(connectionString, SP_Name, IntoParameter.ToArray(), out OutputParameter);
                    scope.Complete();   //不提交就自动回滚
                }
            }
            catch
            {
                throw;
            }

            if (OutputParameter != null)
            {
                int ReturnInt = 0;
                if (OutputParameter.ContainsKey("RETURN_VALUE"))
                {
                    ReturnInt = (int)OutputParameter["RETURN_VALUE"];
                    OutputParameter.Remove("RETURN_VALUE");
                }
                ReturnVal = new Tuple<int, Dictionary<string, object>>(ReturnInt, OutputParameter);
            }

            return ds;
        }
        private static DataSet RunProcedure(string connectionString, string storedProcName, SqlParameter[] parameters, out Dictionary<string, object> OutputParameterValue)
        {
            OutputParameterValue = new Dictionary<string, object>();
            if (string.IsNullOrEmpty(storedProcName))
                return new DataSet();

            if (string.IsNullOrEmpty(connectionString))
                return new DataSet();

            DataSet ds = new DataSet();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand comm = connection.CreateCommand();
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = storedProcName;  //导入存储过程名

                if (parameters != null)
                {
                    foreach (SqlParameter par in parameters)
                    {
                        if (par != null)
                        {
                            if ((par.Direction == ParameterDirection.InputOutput || par.Direction == ParameterDirection.Input) && (par.Value == null))
                            {
                                par.Value = DBNull.Value;
                            }
                            comm.Parameters.Add(par);
                        }
                    }
                }

                using (SqlDataAdapter adpt = new SqlDataAdapter(comm))
                {
                    adpt.Fill(ds);
                    connection.Close();

                    foreach (SqlParameter v in comm.Parameters)
                        if (v.Direction == ParameterDirection.Output || v.Direction == ParameterDirection.InputOutput || v.Direction == ParameterDirection.ReturnValue)
                            OutputParameterValue.Add(v.ParameterName.Replace("@", ""), v.Value);

                    return ds;
                }
            }
        }

        /// <summary>
        /// 批量插入---T是实体类
        /// </summary>
        public static void BulkInsert<T>(SqlConnection conn, string tableName, IList<T> list)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            using (var bulkCopy = new SqlBulkCopy(conn))
            {
                bulkCopy.BatchSize = list.Count;
                bulkCopy.DestinationTableName = tableName;

                var table = new DataTable();
                var props = TypeDescriptor.GetProperties(typeof(T))
                    .Cast<PropertyDescriptor>()
                    .Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
                    .ToArray();

                foreach (var propertyInfo in props)
                {
                    bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
                    table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
                }

                var values = new object[props.Length];
                foreach (var item in list)
                {
                    for (var i = 0; i < values.Length; i++)
                    {
                        values[i] = props[i].GetValue(item);
                    }

                    table.Rows.Add(values);
                }
                //写入数据库
                bulkCopy.WriteToServer(table);
            }

            if (conn.State != ConnectionState.Closed)
                conn.Close();
        }
    }
}
